package com.extJs.demo.servlet;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;
/**JDBC+Service
 * DataService.java
 * @author Mercy
 *
 */
public class DataService {
	private static Connection conn;
	private PreparedStatement pstat;
	private ResultSet rs;
	private User user;

	/**
	 * 执行查询
	 * @return
	 */
	public List<User> getUserInfo() {
		List<User> users = new ArrayList<User>();
		String sql = "SELECT * FROM tb_user";
		try {
			pstat = getConn().prepareStatement(sql);
			rs = pstat.executeQuery();
			while (rs.next()) {
				user = new User();
				user.setId(rs.getInt(1));
				user.setUserName(rs.getString(2));
				user.setDesc(rs.getString(3));
				user.setPhone(rs.getString(4));
				user.setHobby(rs.getString(5));
				user.setBirthday(rs.getDate(6));
				users.add(user);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return users;
	}
	
	public List<User> getUsersByName(String userName){
		List<User> users =new ArrayList<User>();
		User user;
		String sql = "SELECT * FROM tb_user WHERE username = ?";
		try {
			pstat= getConn().prepareStatement(sql);
			pstat.setString(1, userName);
			rs = pstat.executeQuery();
			while(rs.next()){
				user = new User();
				user.setId(rs.getInt(1));
				user.setUserName(rs.getString(2));
				user.setDesc(rs.getString(3));
				user.setPhone(rs.getString(4));
				user.setHobby(rs.getString(5));
				user.setBirthday(rs.getDate(6));
				users.add(user);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	
		return users;
	}

	/**
	 * 获取连接
	 * @return
	 */
	public static Connection getConn() {
		try {
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection(
					"jdbc:mysql://localhost:3306/test", "root", "admin");
		} catch (ClassNotFoundException | SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return conn;
	}

	public void closeAll() throws SQLException {
		if (conn == null) {
			conn.close();
		}
		if (pstat == null) {
			pstat.close();
		}
		if (rs == null) {
			rs.close();
		}
	}

	public static void main(String[] args) throws SQLException {
		DataService ds =  new DataService();
//		System.out.println(ds.getUserInfo());
		System.out.println("****** "+ds.getUsersByName("Abely"));
		JSONArray ja = JSONArray.fromObject(ds.getUserInfo());
		System.out.println(ja);
		JSONObject jo = new JSONObject();
		jo.put("datas", ja);
		System.out.println("****  "+jo);
		ds.closeAll();
	}
}
